import math

import MySQLdb
import re;

class MySql(object):
    """description of class"""
    def __init__(self,host,user,pwd,dbName,charset='utf8' ):
        self.cursor=None;
        self.conn=None;
        # 连接数据库
        self.conn = MySQLdb.connect(host, user, pwd, dbName, charset=charset)
        self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)


    def select(self,sql):
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            list=[]
            # 获取所有记录列表
            results = self.cursor.fetchall()
            if (len(results)==0):
                return None
            for row in results:
                list.append(row)
            #db.cursor.close()
            self.conn.rollback()
            return list;
        except:
            self.conn.rollback();

    def selectOne(self,sql):
        try:
            # 执行SQL语句
            print("执行语句："+sql);
            self.cursor.execute(sql)
            list=[]
            # 获取所有记录列表
            results = self.cursor.fetchall()
            if (len(results)==0):
                return None
            for row in results:
                list.append(row)
                break;
            #db.cursor.close()
            self.conn.rollback()
            return list[0];
        except Exception as e:
            print(repr(e));
            self.conn.rollback();

    def execute(self, sql):
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            self.conn.commit()
            return list;
        except Exception as e:
            self.conn.rollback();
            print(repr(e));

    def __camel_to_snake_case(self,text):
        matches = re.finditer('[A-Z]', text)

        contents = []
        last_start = 0
        for it in matches:
            start, end = it.span()
            if start > 0:
                contents.append(text[last_start:start])

            last_start = start

        contents.append(text[last_start:])
        return '_'.join(contents).lower()

    #插入实体数据
    def insert(self,tabName, entity):
        sql="insert into "+tabName+"(";
        values="";
        for key, value in entity.items():
            sql=sql+self.__camel_to_snake_case(key)+",";
            values=values+"'"+str(value)+"',";
        sql=sql[:-1] +") values("+values[:-1]+")";
        return self.execute(sql);

    def update(self,tabName,keyName, entity):
        sql = "update " + tabName + " set ";
        for key, value in entity.items():
            sql = sql + self.__camel_to_snake_case(key) + "='"+ str(value) + "',";
        sql = sql[:-1] + " where "+keyName+"='"+entity[keyName]+"'";
        return self.execute(sql);

    def selectAll(self,tabName):
        sql = "select * from " + tabName ;
        return self.select(sql);

    def selectPage(self,sql,pageInde,pageSize):
        pageSql="select count(*) cnt from ("+sql+") t";
        total=self.selectOne(pageSql)["cnt"];
        sql=sql+' limit %d,%d' % ((pageInde-1)*pageSize,pageSize)
        list=self.select(sql);
        page={
            "current": pageInde,
            "total": total,
            "pages": math.ceil(total/int(pageSize)),
            "optimizeCountSql": True,
            "size": pageSize,
            "records":list
        }
        return page;